package com.example.servlettext;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class ContextServletTest extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        ServletContext context = this.getServletContext();

        try (InputStream stream = context.getResourceAsStream("/WEB-INF/classes/jdbc.properties")) {
            Properties properties = new Properties();
            properties.load(stream);
            String dbName = properties.getProperty("dbName");
            String userName = properties.getProperty("userName");
            String userPasswd = properties.getProperty("userPasswd");
            String url = properties.getProperty("url");
            url = url + dbName + "?user=" + userName + "&password=" + userPasswd +
                    "&useUnicode=true&characterEncoding=utf-8";

            Class.forName("com.mysql.cj.jdbc.Driver");
            try (Connection conn = DriverManager.getConnection(url);
                 Statement statement = conn.createStatement()) {

                String sql = "select * from student ORDER BY stu_speciality DESC, stu_politicalstatus ASC";
                try (ResultSet rs = statement.executeQuery(sql)) {
                    out.println("<html><head><title>学生信息</title></head><body>");
                    out.println("<table border='1' style='width:100%'>");
                    out.println("<caption style='font-size:x-large'>学生信息表</caption>");
                    out.println("<tr style='text-align:center'>");
                    out.println("<th>学号</th><th>姓名</th><th>性别</th><th>政治身份</th>");
                    out.println("<th>出生日期</th><th>身份证号</th><th>所在系部</th></tr>");

                    while (rs.next()) {
                        out.println("<tr style='text-align:center'>");
                        out.println("<td>" + rs.getString(1) + "</td>");
                        out.println("<td>" + rs.getString(2) + "</td>");
                        out.println("<td>" + rs.getString(3) + "</td>");
                        out.println("<td>" + rs.getString(4) + "</td>");
                        out.println("<td>" + rs.getString(5) + "</td>");
                        out.println("<td>" + rs.getString(6) + "</td>");
                        out.println("<td>" + rs.getString(7) + "</td>");
                        out.println("</tr>");
                    }
                    out.println("</table></body></html>");
                }
            }
        } catch (Exception e) {
            out.println("<html><body><h2>系统错误</h2><p>" + e.getMessage() + "</p></body></html>");
            e.printStackTrace();
        } finally {
            out.close();
        }
    }

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doPost(request, response);
    }
}
